﻿using System;
using System.Collections.Generic;
using System.Linq;
using Model;
using DBUtility;
using MySql.Data.MySqlClient;

namespace DAL
{
    public class DurableSpecRepository
    {
        // 添加耐用品规格
        public int AddDurableSpec(DurableSpec durableSpec)
        {
            string sql = @"INSERT INTO durablespec 
                            (SpecificationDescription, Type, DetailedType, Color, 
                             MaxUsageCount, MaxUsageDays, MaxUsageCountAfterCleaning, 
                             MaxUsageDaysAfterCleaning, MaxCleaningCount) 
                           VALUES 
                            (@SpecificationDescription, @Type, @DetailedType, @Color, 
                             @MaxUsageCount, @MaxUsageDays, @MaxUsageCountAfterCleaning, 
                             @MaxUsageDaysAfterCleaning, @MaxCleaningCount)";

            MySqlParameter[] parameters = {
                new MySqlParameter("@SpecificationDescription", durableSpec.SpecificationDescription),
                new MySqlParameter("@Type", durableSpec.Type),
                new MySqlParameter("@DetailedType", durableSpec.DetailedType),
                new MySqlParameter("@Color", durableSpec.Color),
                new MySqlParameter("@MaxUsageCount", durableSpec.MaxUsageCount),
                new MySqlParameter("@MaxUsageDays", durableSpec.MaxUsageDays),
                new MySqlParameter("@MaxUsageCountAfterCleaning", durableSpec.MaxUsageCountAfterCleaning),
                new MySqlParameter("@MaxUsageDaysAfterCleaning", durableSpec.MaxUsageDaysAfterCleaning),
                new MySqlParameter("@MaxCleaningCount", durableSpec.MaxCleaningCount)
            };

            return DBHelper.ExecuteNonQuery(sql, parameters);
        }

        // 删除耐用品规格
        public int DeleteDurableSpec(int durableSpecId)
        {
            string sql = "DELETE FROM durablespec WHERE DurableSpecId = @DurableSpecId";
            MySqlParameter[] parameters = { new MySqlParameter("@DurableSpecId", durableSpecId) };
            return DBHelper.ExecuteNonQuery(sql, parameters);
        }

        // 更新耐用品规格信息
        public int UpdateDurableSpec(DurableSpec durableSpec)
        {
            string sql = @"UPDATE durablespec 
                            SET SpecificationDescription = @SpecificationDescription, 
                                Type = @Type, 
                                DetailedType = @DetailedType, 
                                Color = @Color, 
                                MaxUsageCount = @MaxUsageCount, 
                                MaxUsageDays = @MaxUsageDays, 
                                MaxUsageCountAfterCleaning = @MaxUsageCountAfterCleaning, 
                                MaxUsageDaysAfterCleaning = @MaxUsageDaysAfterCleaning, 
                                MaxCleaningCount = @MaxCleaningCount 
                          WHERE DurableSpecId = @DurableSpecId";

            MySqlParameter[] parameters = {
                new MySqlParameter("@SpecificationDescription", durableSpec.SpecificationDescription),
                new MySqlParameter("@Type", durableSpec.Type),
                new MySqlParameter("@DetailedType", durableSpec.DetailedType),
                new MySqlParameter("@Color", durableSpec.Color),
                new MySqlParameter("@MaxUsageCount", durableSpec.MaxUsageCount),
                new MySqlParameter("@MaxUsageDays", durableSpec.MaxUsageDays),
                new MySqlParameter("@MaxUsageCountAfterCleaning", durableSpec.MaxUsageCountAfterCleaning),
                new MySqlParameter("@MaxUsageDaysAfterCleaning", durableSpec.MaxUsageDaysAfterCleaning),
                new MySqlParameter("@MaxCleaningCount", durableSpec.MaxCleaningCount),
                new MySqlParameter("@DurableSpecId", durableSpec.DurableSpecId)
            };

            return DBHelper.ExecuteNonQuery(sql, parameters);
        }

        // 查询所有耐用品规格
        public List<DurableSpec> GetAllDurableSpecs()
        {
            string sql = "SELECT * FROM durablespec";
            return DBHelper.ExecuteQuery(sql, null, MapDurableSpec);
        }

        // 查询单个耐用品规格
        public DurableSpec GetDurableSpecById(int durableSpecId)
        {
            string sql = "SELECT * FROM durablespec WHERE DurableSpecId = @DurableSpecId";
            MySqlParameter[] parameters = { new MySqlParameter("@DurableSpecId", durableSpecId) };
            var durableSpecs = DBHelper.ExecuteQuery(sql, parameters, MapDurableSpec);
            return durableSpecs.FirstOrDefault();
        }

        // 映射数据库查询结果到 DurableSpec 对象
        private DurableSpec MapDurableSpec(MySqlDataReader reader)
        {
            return new DurableSpec
            {
                DurableSpecId = (int)reader["DurableSpecId"],
                SpecificationDescription = reader["SpecificationDescription"] is DBNull ? null : reader["SpecificationDescription"].ToString(),
                Type = reader["Type"].ToString(),
                DetailedType = reader["DetailedType"].ToString(),
                Color = reader["Color"].ToString(),
                MaxUsageCount = (int)reader["MaxUsageCount"],
                MaxUsageDays = (int)reader["MaxUsageDays"],
                MaxUsageCountAfterCleaning = (int)reader["MaxUsageCountAfterCleaning"],
                MaxUsageDaysAfterCleaning = (int)reader["MaxUsageDaysAfterCleaning"],
                MaxCleaningCount = (int)reader["MaxCleaningCount"]
            };
        }
    }
}